package common.splitpage.sql;

import common.splitpage.Condition;
import common.splitpage.PageInfo;

public class DB2PageSql implements IPageSql{
	
	public final static String fromSplit = "_";
	
	private int pageType =2 ;
	private int startRow = 0;
	private int endRow = 0;
	private String sql;
	
	public void setPageInfo(PageInfo pageInfo){
		this.pageType = pageInfo.getPageType();
		Condition condition = pageInfo.getCondition();
		this.startRow = condition.getStartRow();
		this.endRow = condition.getEndRow();
		this.sql = condition.getSql();
	}
	
	public String getCountSql(){
		String sqlArr[] = null;
		if(sql.indexOf(fromSplit+"from")!=-1){
			sqlArr = sql.split(fromSplit+"from");
		}else{
			sqlArr = sql.split("from");
		}
		String countSql = "select count(*) from " + sqlArr[1].split("order by")[0];
		return countSql;
	}
	
	public String getPageSql(){
		if(this.pageType==1){
			return this.getSql1(sql);
		}
		else if(this.pageType==2){
			return this.getSql2(sql);
		}else if(this.pageType==3){
			return this.getSql3(sql);
		}
		else{
			return this.sql;
		}
	}
	
	private String getSql1(String sql) {//分页SQL语句组装方式一
		sql = sql.trim();
		StringBuffer sb = new StringBuffer(100);
		sb.append("(");
		sb.append(sql);
		sb.append(" fetch first ");
		sb.append(endRow);
		sb.append(" rows only) \n");
		if(startRow-1>0){
			sb.append("except \n");
			sb.append("(");
			sb.append(sql);
			sb.append(" fetch first ");
			sb.append(startRow-1);
			sb.append(" rows only) \n");
		}
		return sb.toString();
	}
	
	//注意:有子查询就以"_from"为分隔符号
	private String getSql2(String sql) {//分页SQL语句组装方式二
		sql = sql.trim();
		StringBuffer sb = new StringBuffer(100);
		sb.append("with temp(");
		
		//组装查询的列数
		String columnNames = "";
		int selectIndex = 6;
		if(sql.indexOf(fromSplit+"from ")!=-1){//有子查询:以"_from"为分隔符号
			columnNames = sql.split(fromSplit+"from ")[0];
			columnNames = columnNames.substring(selectIndex).trim();
			sql = sql.replaceFirst(fromSplit+"from", "from");
		}else{
			int fromIndex = sql.indexOf("from");
			columnNames = sql.substring(selectIndex,fromIndex).trim();
		}
		String[] columnNamesArr = columnNames.split(",");
		for(int i=0;i<columnNamesArr.length;i++){
			String splitStr = ",";
			if(i==columnNamesArr.length-1) splitStr = "";
			String columnName = columnNamesArr[i].trim();
			if(columnName.indexOf(" as ")==-1){//加空格解决列名中有"as"的问题
				sb.append(columnName + splitStr);
			}else{
				sb.append(columnName.split(" as ")[1].trim()+splitStr);//分割的as要前后有空格,不然像"1 as fpass_word"就会分割错误
			}
		}
		
		sb.append(") as ( \n");
		
		sb.append(sql);
		sb.append(" fetch first " + endRow + " rows only");
		sb.append("\n) \n");
		
		sb.append("SELECT * FROM (Select temp.*,rownumber() over() as num from temp) AS temp \n");
		sb.append("WHERE temp.num BETWEEN " + startRow + " AND " + endRow);
		return sb.toString();
	}
	
	private String getSql3(String sql) {//分页SQL语句组装方式三
		sql = sql.trim();
		StringBuffer sb = new StringBuffer(100);
		String[] sqlArr = sql.split("from");
		sb.append("SELECT * FROM ("+sqlArr[0]+",rownumber() over() as num from"+sqlArr[1]+") AS temp \n");
		sb.append("WHERE temp.num BETWEEN " + startRow + " AND " + endRow);
		return sb.toString();
	}	
	
	public static void main(String[] args) {
	
//		System.out.println(util.getVOFieldName("fbill_code"));
//		System.out.println(util.getCountSql(" select fid from table order by fid "));
//		
//		String sql = "select fid, FBILL_CODE from TFM_FS_T_BILL_USE where FBILL_CODE >='0006030'";
		
		String sql = " select qw,(select * from table ) as tempColun,asd,(select * from tab) as fg _from table1,(select * from table) ";
		//System.out.println(util.getSql2(sql));
	}

}
